use std::{process::exit, time::Duration};

use log::{error, info};
use sqlx::{MySql, Pool, prelude::FromRow};

use crate::config::Server;

#[derive(Debug, Default)]
pub struct DBInfo {
    pub db_host: String,
    pub db_port: String,
    pub db_username: String,
    pub db_password: String,
}

#[derive(Debug, FromRow)]
pub struct YRBA {
    lscn: Option<String>,
    ucmt_scn: Option<String>,
}

pub const DB_NAME: &'static str = "dataxone_pmon";

#[derive(Debug, Clone)]
pub struct Client {
    pool: Pool<MySql>,
}

impl Client {
    pub async fn new(db_info: &DBInfo) -> Self {
        let port: u16 = db_info.db_port.parse().unwrap();
        let options = sqlx::mysql::MySqlConnectOptions::new()
            .host(&db_info.db_host)
            .port(port as u16)
            .username(&db_info.db_username)
            .password(&db_info.db_password)
            .ssl_mode(sqlx::mysql::MySqlSslMode::Disabled)
            .timezone(Some(String::from("+08:00")))
            .database(&DB_NAME);

        let result = sqlx::mysql::MySqlPoolOptions::new()
            .max_connections(1)
            .min_connections(1)
            .max_lifetime(None)
            .acquire_timeout(Duration::from_secs(10800))
            .idle_timeout(Duration::from_secs(10800))
            .connect_with(options)
            .await;

        Client {
            pool: result.unwrap(),
        }
    }

    pub async fn query_log_pos(&self, s: &Server) -> Option<String> {
        let sql = format!(
            "select LSCN as lscn, UCMT_SCN as ucmt_scn from {}.yrba where qnm = ?",
            DB_NAME
        );

        // thread 'monica' panicked at src\db\mod.rs:54:102:
        // called `Result::unwrap()` on an `Err` value: PoolTimedOut
        // 1、cause: error returned from database: 1159 (08S01): Got timeout reading communication packets
        // 2、Database data fetch failed, cause: pool timed out while waiting for an open connection
        // 连接超时，需保持长连接
        let rows = match sqlx::query_as::<_, YRBA>(&sql)
            .bind(&s.service_name)
            .fetch_all(&self.pool)
            .await
        {
            Ok(r) => r,
            Err(e) => {
                error!(
                    "xlsx:{:<2} Database data fetch failed, cause: {}",
                    s.rid, e
                );
                exit(-1);
            }
        };

        if rows.len() == 0 {
            None
        } else {
            let row: &YRBA = rows.iter().next().unwrap();
            let mut yrba = match &row.lscn {
                Some(value) => format!("{},", value),
                None => String::from(","),
            };

            yrba = match &row.ucmt_scn {
                Some(value) => format!("{}{}", yrba, value),
                None => format!("{}", yrba),
            };

            if yrba == "," {
                return None;
            }

            Some(yrba)
        }
    }

    pub async fn add_whitelist(&self, s: &Server) {
        let sql = r"SET @flow_id := ''; set @service_name = ?;\n
        select flow_id INTO @flow_id from automation_dsg_db_new.rel_servicename_flow_engine_info where service_name = @service_name limit 1; \n
        insert into automation_dsg_db_new.op_disaster_recovery_group_map_tb (flow_id,flow_name,module_id) select flow_id, @service_name, module_id FROM automation_dsg_db_new.rel_servicename_flow_engine_info a,automation_dsg_db_new.op_engine_info_tb b where a.flow_id = @flow_id and a.engine_id=b.e_id;";

        if let Err(e) = sqlx::query(&sql).bind(&s.service_name).fetch_all(&self.pool).await {
            error!(
                "xlsx:{:<2} add_whitelist:: Database data fetch failed, cause: {}",
                s.rid, e
            );
        } else {
            info!(
                "xlsx:{:<2} add_whitelist:: ok",
                s.rid
            );
        }
    }

    pub async fn remove_whitelist(&self, s: &Server) {
        let sql = "delete from automation_dsg_db_new.op_disaster_recovery_group_map_tb where flow_name = ?";
        if let Err(e) = sqlx::query(&sql).fetch_all(&self.pool).await {
            error!(
                "xlsx:{:<2} remove_whitelist:: Database data fetch failed, cause: {}",
                s.rid, e
            );
        } else {
            info!(
                "xlsx:{:<2} remove_whitelist:: ok",
                s.rid
            );
        }
    }
}
